-- 从运单表获取最新一天数据
with waybill_new_data as (
    select
        id
        ,waybill_no
        ,waybill_status_code
        ,goods_type_id
        ,goods_type_code
        ,goods_type_name
        ,goods_name
        ,cast(package_length AS DECIMAL(14,2)) as package_length
        ,cast(package_wide AS DECIMAL(14,2)) as  package_wide
        ,cast(package_high AS DECIMAL(14,2)) as  package_high
        ,cast(package_volume AS DECIMAL(14,2)) as  package_volume
        ,cast(package_total_volume AS DECIMAL(14,2)) as  package_total_volume
        ,cast(package_charge_weight AS DECIMAL(14,2)) as  package_charge_weight
        ,collect_staff_code
        ,collect_time
        ,dispatch_staff_code
        ,dispatch_staff_name
        ,dispatch_time
        ,dispatch_network_code
        ,dispatch_network_name
        ,exception_code
        ,terminal_dispatch_code
        ,nullif(split(terminal_dispatch_code, ',')[0], '') AS first_code
        ,nullif(split(terminal_dispatch_code, ',')[1], '') AS second_code
        ,nullif(split(terminal_dispatch_code, ',')[2], '') AS third_code
        ,is_sign
        ,sign_network_code
        ,sign_network_name
        ,sign_finance_code
        ,sign_time
        ,is_abnormal
        ,abnormal_reg_network_code
        ,abnormal_reg_time
        ,customer_order_id
        ,waybill_source_code
        ,waybill_source_name
        ,is_void
        ,is_refund
        ,customer_code
        ,customer_name
        ,sender_name
        ,sender_province_id
        ,sender_city_id
        ,sender_area_id
        ,sender_township
        ,sender_street
        ,sender_detailed_address
        ,origin_id
        ,pick_network_code
        ,pick_network_name
        ,cast(freight AS DECIMAL(14,2)) as  freight
        ,cast(total_freight AS DECIMAL(14,2)) as  total_freight
        ,pick_finance_code
        ,dispatch_finance_code
        ,receiver_name
        ,receiver_province_id
        ,receiver_city_id
        ,receiver_area_id
        ,receiver_township
        ,receiver_street
        ,receiver_detailed_address
        ,destination_id
        ,receiver_sorting_code
        ,input_time
        ,input_network_code
        ,cast(settlement_weight AS DECIMAL(14,2)) as  settlement_weight
        ,order_source_code
        ,last_update_time
        ,is_delete
        ,cast(package_collect_weight AS DECIMAL(14,2)) as  package_collect_weight
        ,cast(package_receipt_weight AS DECIMAL(14,2)) as  package_receipt_weight
        ,cast(package_arrival_weight AS DECIMAL(14,2)) as  package_arrival_weight
        ,cast(package_inside_charge_weight AS DECIMAL(14,2)) as  package_inside_charge_weight
        ,waybill_weight
        ,is_business
        ,is_receive
        ,express_type_code
        ,express_type_name
        ,pick_finance_name
        ,settlement_code
        ,settlement_name
        ,customer_type
        ,cast(from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss") as timestamp) as etl_load_time
        ,cast(package_total_weight AS DECIMAL(14,2)) as package_total_weight
        ,receiver_mobile_phone
        ,delivery_time
        ,dispatch_network_id
        ,pick_network_id
        ,subscribe_source_code as ordersource_code
        ,subscribe_source_name as ordersource_name
        ,inspection_method
        ,package_number
        ,box_standard_code
        ,box_standard_name
        ,box_number
        ,box_price
        ,collect_staff_name
        ,exception_reason
        ,sign_network_id
        ,sign_finance_name
        ,is_real_name
        ,id_no
        ,abnormal_reg_network_name
        ,order_id
        ,send_code
        ,send_name
        ,dispatch_code
        ,dispatch_name
        ,is_need_receipt
        ,receipt_no
        ,is_settlement
        ,settlement_time
        ,sender_mobile_phone
        ,sender_telphone
        ,sender_country_id
        ,sender_country_name
        ,sender_province_name
        ,sender_city_name
        ,sender_area_name
        ,sender_postal_code
        ,sender_email
        ,origin_code
        ,origin_name
        ,package_cost
        ,tax
        ,handicraft_fee
        ,other_fee
        ,receive_pay_fee
        ,pick_finance_id
        ,dispatch_finance_name
        ,express_type_id
        ,insured
        ,insured_amount
        ,insured_fee
        ,cod_need
        ,cod_money
        ,cod_fee
        ,settlement_id
        ,paid_mode_code
        ,paid_mode_name
        ,receiver_company
        ,receiver_telphone
        ,receiver_country_id
        ,receiver_country_name
        ,receiver_province_name
        ,receiver_city_name
        ,receiver_area_name
        ,receiver_postal_code
        ,receiver_email
        ,destination_code
        ,destination_name
        ,input_staff_code
        ,input_staff_name
        ,input_network_name
        ,coupon_code
        ,coupon_amount
        ,remarks
        ,prints_number
        ,bill_no
        ,is_cash
        ,is_cod_receive
        ,currency_code
        ,currency_name
        ,customer_id
        ,is_plaintext
        ,refund_time
        ,input_staff_by
        ,real_name
        ,id_no_type
        ,sex
        ,distribute_finance_id
        ,distribute_finance_code
        ,distribute_finance_name
        ,is_distribute_scan
        ,is_arrival_scan
        ,miss_flag
        ,customer_network_code
        ,customer_network_name
        ,calculate_fee_desc
        ,calculate_fee_code
        ,printer_counterfoil
        ,quotetype_code
        ,receipt_fee
        ,package_center_arrival_weight
        ,get_json_object(invoice_no,'$.stationCode') as station_code
        ,get_json_object(invoice_no,'$.stationUniqueNumber') as station_unique_Number
        ,invoice_no
        ,date_format(input_time, 'yyyy-MM-dd') as dt
    from jms_ods.yl_oms_oms_waybill
    where dt='{{ execution_date | cst_ds }}'
    and date_format(input_time,'yyyy-MM-dd') >= date_add('{{ execution_date | cst_ds }}', -60)
),
-- 获取一段码数据
first_code_date as (
    select code,center_name,center_code
    from jms_dim.dim_lmdm_sys_first_code
    where is_enable=1 and is_delete = 1
    group by code,center_name,center_code
),
-- 获取运单去重表近60天数据
waybill_history_data as (
    select
        id
        ,waybill_no
        ,waybill_status_code
        ,goods_type_id
        ,goods_type_code
        ,goods_type_name
        ,goods_name
        ,package_length
        ,package_wide
        ,package_high
        ,package_volume
        ,package_total_volume
        ,package_charge_weight
        ,collect_staff_code
        ,collect_time
        ,dispatch_staff_code
        ,dispatch_staff_name
        ,dispatch_time
        ,dispatch_network_code
        ,dispatch_network_name
        ,exception_code
        ,terminal_dispatch_code
        ,first_code
        ,second_code
        ,third_code
        ,is_sign
        ,sign_network_code
        ,sign_network_name
        ,sign_finance_code
        ,sign_time
        ,is_abnormal
        ,abnormal_reg_network_code
        ,abnormal_reg_time
        ,customer_order_id
        ,waybill_source_code
        ,waybill_source_name
        ,is_void
        ,is_refund
        ,customer_code
        ,customer_name
        ,sender_name
        ,sender_province_id
        ,sender_city_id
        ,sender_area_id
        ,sender_township
        ,sender_street
        ,sender_detailed_address
        ,origin_id
        ,pick_network_code
        ,pick_network_name
        ,freight
        ,total_freight
        ,pick_finance_code
        ,dispatch_finance_code
        ,receiver_name
        ,receiver_province_id
        ,receiver_city_id
        ,receiver_area_id
        ,receiver_township
        ,receiver_street
        ,receiver_detailed_address
        ,destination_id
        ,receiver_sorting_code
        ,input_time
        ,input_network_code
        ,settlement_weight
        ,order_source_code
        ,last_update_time
        ,is_delete
        ,package_collect_weight
        ,package_receipt_weight
        ,package_arrival_weight
        ,package_inside_charge_weight
        ,waybill_weight
        ,is_business
        ,is_receive
        ,express_type_code
        ,express_type_name
        ,pick_finance_name
        ,settlement_code
        ,settlement_name
        ,customer_type
        ,center_name
        ,center_code
        ,ordersource_code
        ,ordersource_name
        ,etl_load_time
        ,package_total_weight
        ,receiver_mobile_phone
        ,delivery_time
        ,dispatch_network_id
        ,pick_network_id
        ,inspection_method
        ,package_number
        ,box_standard_code
        ,box_standard_name
        ,box_number
        ,box_price
        ,collect_staff_name
        ,exception_reason
        ,sign_network_id
        ,sign_finance_name
        ,is_real_name
        ,id_no
        ,abnormal_reg_network_name
        ,order_id
        ,send_code
        ,send_name
        ,dispatch_code
        ,dispatch_name
        ,is_need_receipt
        ,receipt_no
        ,is_settlement
        ,settlement_time
        ,sender_mobile_phone
        ,sender_telphone
        ,sender_country_id
        ,sender_country_name
        ,sender_province_name
        ,sender_city_name
        ,sender_area_name
        ,sender_postal_code
        ,sender_email
        ,origin_code
        ,origin_name
        ,package_cost
        ,tax
        ,handicraft_fee
        ,other_fee
        ,receive_pay_fee
        ,pick_finance_id
        ,dispatch_finance_name
        ,express_type_id
        ,insured
        ,insured_amount
        ,insured_fee
        ,cod_need
        ,cod_money
        ,cod_fee
        ,settlement_id
        ,paid_mode_code
        ,paid_mode_name
        ,receiver_company
        ,receiver_telphone
        ,receiver_country_id
        ,receiver_country_name
        ,receiver_province_name
        ,receiver_city_name
        ,receiver_area_name
        ,receiver_postal_code
        ,receiver_email
        ,destination_code
        ,destination_name
        ,input_staff_code
        ,input_staff_name
        ,input_network_name
        ,coupon_code
        ,coupon_amount
        ,remarks
        ,prints_number
        ,bill_no
        ,is_cash
        ,is_cod_receive
        ,currency_code
        ,currency_name
        ,customer_id
        ,is_plaintext
        ,refund_time
        ,input_staff_by
        ,real_name
        ,id_no_type
        ,sex
        ,distribute_finance_id
        ,distribute_finance_code
        ,distribute_finance_name
        ,is_distribute_scan
        ,is_arrival_scan
        ,miss_flag
        ,customer_network_code
        ,customer_network_name
        ,calculate_fee_desc
        ,calculate_fee_code
        ,printer_counterfoil
        ,quotetype_code
        ,receipt_fee
        ,package_center_arrival_weight
        ,station_code
        ,station_unique_Number
        ,invoice_no
        ,dt
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt >= date_sub('{{ execution_date | cst_ds }}',60) and dt < '{{ execution_date | cst_ds }}'
),
-- 获取运单表最近1天数据与一段码、订单来源表关联
waybill_new_data_process as (
    select
        t1.id
        ,t1.waybill_no
        ,t1.waybill_status_code
        ,t1.goods_type_id
        ,t1.goods_type_code
        ,t1.goods_type_name
        ,t1.goods_name
        ,t1.package_length
        ,t1.package_wide
        ,t1.package_high
        ,t1.package_volume
        ,t1.package_total_volume
        ,t1.package_charge_weight
        ,t1.collect_staff_code
        ,t1.collect_time
        ,t1.dispatch_staff_code
        ,t1.dispatch_staff_name
        ,t1.dispatch_time
        ,t1.dispatch_network_code
        ,t1.dispatch_network_name
        ,t1.exception_code
        ,t1.terminal_dispatch_code
        ,t1.first_code
        ,t1.second_code
        ,t1.third_code
        ,t1.is_sign
        ,t1.sign_network_code
        ,t1.sign_network_name
        ,t1.sign_finance_code
        ,t1.sign_time
        ,t1.is_abnormal
        ,t1.abnormal_reg_network_code
        ,t1.abnormal_reg_time
        ,t1.customer_order_id
        ,t1.waybill_source_code
        ,t1.waybill_source_name
        ,t1.is_void
        ,t1.is_refund
        ,t1.customer_code
        ,t1.customer_name
        ,t1.sender_name
        ,t1.sender_province_id
        ,t1.sender_city_id
        ,t1.sender_area_id
        ,t1.sender_township
        ,t1.sender_street
        ,t1.sender_detailed_address
        ,t1.origin_id
        ,t1.pick_network_code
        ,t1.pick_network_name
        ,t1.freight
        ,t1.total_freight
        ,t1.pick_finance_code
        ,t1.dispatch_finance_code
        ,t1.receiver_name
        ,t1.receiver_province_id
        ,t1.receiver_city_id
        ,t1.receiver_area_id
        ,t1.receiver_township
        ,t1.receiver_street
        ,t1.receiver_detailed_address
        ,t1.destination_id
        ,t1.receiver_sorting_code
        ,t1.input_time
        ,t1.input_network_code
        ,t1.settlement_weight
        ,t1.order_source_code
        ,t1.last_update_time
        ,t1.is_delete
        ,t1.package_collect_weight
        ,t1.package_receipt_weight
        ,t1.package_arrival_weight
        ,t1.package_inside_charge_weight
        ,t1.waybill_weight
        ,t1.is_business
        ,t1.is_receive
        ,t1.express_type_code
        ,t1.express_type_name
        ,t1.pick_finance_name
        ,t1.settlement_code
        ,t1.settlement_name
        ,t1.customer_type
        ,t2.center_name
        ,t2.center_code
        ,t1.ordersource_code
        ,t1.ordersource_name
        ,t1.etl_load_time
        ,t1.package_total_weight
        ,t1.receiver_mobile_phone
        ,t1.delivery_time
        ,t1.dispatch_network_id
        ,t1.pick_network_id
        ,t1.inspection_method
        ,t1.package_number
        ,t1.box_standard_code
        ,t1.box_standard_name
        ,t1.box_number
        ,t1.box_price
        ,t1.collect_staff_name
        ,t1.exception_reason
        ,t1.sign_network_id
        ,t1.sign_finance_name
        ,t1.is_real_name
        ,t1.id_no
        ,t1.abnormal_reg_network_name
        ,t1.order_id
        ,t1.send_code
        ,t1.send_name
        ,t1.dispatch_code
        ,t1.dispatch_name
        ,t1.is_need_receipt
        ,t1.receipt_no
        ,t1.is_settlement
        ,t1.settlement_time
        ,t1.sender_mobile_phone
        ,t1.sender_telphone
        ,t1.sender_country_id
        ,t1.sender_country_name
        ,t1.sender_province_name
        ,t1.sender_city_name
        ,t1.sender_area_name
        ,t1.sender_postal_code
        ,t1.sender_email
        ,t1.origin_code
        ,t1.origin_name
        ,t1.package_cost
        ,t1.tax
        ,t1.handicraft_fee
        ,t1.other_fee
        ,t1.receive_pay_fee
        ,t1.pick_finance_id
        ,t1.dispatch_finance_name
        ,t1.express_type_id
        ,t1.insured
        ,t1.insured_amount
        ,t1.insured_fee
        ,t1.cod_need
        ,t1.cod_money
        ,t1.cod_fee
        ,t1.settlement_id
        ,t1.paid_mode_code
        ,t1.paid_mode_name
        ,t1.receiver_company
        ,t1.receiver_telphone
        ,t1.receiver_country_id
        ,t1.receiver_country_name
        ,t1.receiver_province_name
        ,t1.receiver_city_name
        ,t1.receiver_area_name
        ,t1.receiver_postal_code
        ,t1.receiver_email
        ,t1.destination_code
        ,t1.destination_name
        ,t1.input_staff_code
        ,t1.input_staff_name
        ,t1.input_network_name
        ,t1.coupon_code
        ,t1.coupon_amount
        ,t1.remarks
        ,t1.prints_number
        ,t1.bill_no
        ,t1.is_cash
        ,t1.is_cod_receive
        ,t1.currency_code
        ,t1.currency_name
        ,t1.customer_id
        ,t1.is_plaintext
        ,t1.refund_time
        ,t1.input_staff_by
        ,t1.real_name
        ,t1.id_no_type
        ,t1.sex
        ,t1.distribute_finance_id
        ,t1.distribute_finance_code
        ,t1.distribute_finance_name
        ,t1.is_distribute_scan
        ,t1.is_arrival_scan
        ,t1.miss_flag
        ,t1.customer_network_code
        ,t1.customer_network_name
        ,t1.calculate_fee_desc
        ,t1.calculate_fee_code
        ,t1.printer_counterfoil
        ,t1.quotetype_code
        ,t1.receipt_fee
        ,t1.package_center_arrival_weight
        ,t1.station_code
        ,t1.station_unique_number
        ,t1.invoice_no
        ,t1.dt
    from waybill_new_data t1
    left join first_code_date t2
    on t1.first_code = t2.code
),
-- 将最近一天数据与最近60天数据合并去重
way_result as (
    select *
        ,row_number() OVER (PARTITION BY waybill_no ORDER BY etl_load_time DESC) AS rnk  --排序
    from (
       select * from waybill_history_data
       union all
       select * from waybill_new_data_process
    ) waybill_union
)
-- 数据落地，小文件控制
insert overwrite table jms_dwd.dwd_yl_oms_oms_waybill_incre_dt partition(dt)
select
    id
    ,waybill_no
    ,waybill_status_code
    ,goods_type_id
    ,goods_type_code
    ,goods_type_name
    ,goods_name
    ,package_length
    ,package_wide
    ,package_high
    ,package_volume
    ,package_total_volume
    ,package_charge_weight
    ,collect_staff_code
    ,collect_time
    ,dispatch_staff_code
    ,dispatch_staff_name
    ,dispatch_time
    ,dispatch_network_code
    ,dispatch_network_name
    ,exception_code
    ,terminal_dispatch_code
    ,first_code
    ,second_code
    ,third_code
    ,is_sign
    ,sign_network_code
    ,sign_network_name
    ,sign_finance_code
    ,sign_time
    ,is_abnormal
    ,abnormal_reg_network_code
    ,abnormal_reg_time
    ,customer_order_id
    ,waybill_source_code
    ,waybill_source_name
    ,is_void
    ,is_refund
    ,customer_code
    ,customer_name
    ,sender_name
    ,sender_province_id
    ,sender_city_id
    ,sender_area_id
    ,sender_township
    ,sender_street
    ,sender_detailed_address
    ,origin_id
    ,pick_network_code
    ,pick_network_name
    ,freight
    ,total_freight
    ,pick_finance_code
    ,dispatch_finance_code
    ,receiver_name
    ,receiver_province_id
    ,receiver_city_id
    ,receiver_area_id
    ,receiver_township
    ,receiver_street
    ,receiver_detailed_address
    ,destination_id
    ,receiver_sorting_code
    ,input_time
    ,input_network_code
    ,settlement_weight
    ,order_source_code
    ,last_update_time
    ,is_delete
    ,package_collect_weight
    ,package_receipt_weight
    ,package_arrival_weight
    ,package_inside_charge_weight
    ,waybill_weight
    ,is_business
    ,is_receive
    ,express_type_code
    ,express_type_name
    ,pick_finance_name
    ,settlement_code
    ,settlement_name
    ,customer_type
    ,center_name
    ,center_code
    ,ordersource_code
    ,ordersource_name
    ,etl_load_time
    ,package_total_weight
    ,receiver_mobile_phone
    ,delivery_time
    ,dispatch_network_id
    ,pick_network_id
    ,inspection_method
    ,package_number
    ,box_standard_code
    ,box_standard_name
    ,box_number
    ,box_price
    ,collect_staff_name
    ,exception_reason
    ,sign_network_id
    ,sign_finance_name
    ,is_real_name
    ,id_no
    ,abnormal_reg_network_name
    ,order_id
    ,send_code
    ,send_name
    ,dispatch_code
    ,dispatch_name
    ,is_need_receipt
    ,receipt_no
    ,is_settlement
    ,settlement_time
    ,sender_mobile_phone
    ,sender_telphone
    ,sender_country_id
    ,sender_country_name
    ,sender_province_name
    ,sender_city_name
    ,sender_area_name
    ,sender_postal_code
    ,sender_email
    ,origin_code
    ,origin_name
    ,package_cost
    ,tax
    ,handicraft_fee
    ,other_fee
    ,receive_pay_fee
    ,pick_finance_id
    ,dispatch_finance_name
    ,express_type_id
    ,insured
    ,insured_amount
    ,insured_fee
    ,cod_need
    ,cod_money
    ,cod_fee
    ,settlement_id
    ,paid_mode_code
    ,paid_mode_name
    ,receiver_company
    ,receiver_telphone
    ,receiver_country_id
    ,receiver_country_name
    ,receiver_province_name
    ,receiver_city_name
    ,receiver_area_name
    ,receiver_postal_code
    ,receiver_email
    ,destination_code
    ,destination_name
    ,input_staff_code
    ,input_staff_name
    ,input_network_name
    ,coupon_code
    ,coupon_amount
    ,remarks
    ,prints_number
    ,bill_no
    ,is_cash
    ,is_cod_receive
    ,currency_code
    ,currency_name
    ,customer_id
    ,is_plaintext
    ,refund_time
    ,input_staff_by
    ,real_name
    ,id_no_type
    ,sex
    ,distribute_finance_id
    ,distribute_finance_code
    ,distribute_finance_name
    ,is_distribute_scan
    ,is_arrival_scan
    ,miss_flag
    ,customer_network_code
    ,customer_network_name
    ,calculate_fee_desc
    ,calculate_fee_code
    ,printer_counterfoil
    ,quotetype_code
    ,receipt_fee
    ,package_center_arrival_weight
    ,station_code
    ,station_unique_number
    ,invoice_no
    ,dt
from way_result where rnk = 1
distribute by dt, abs(hash(waybill_no)) % 80 ;